using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web.Security;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

public class DataAccess : IDisposable
{

    #region Variables and Constructors
    private Database db = null;
    public DataAccess(string sConn)
    {
        db = new SqlDatabase(sConn);
    }
    #endregion

    #region ExecuteNonQuery

    //No parameters only Sql Command string
    public int ExecuteNonQuery(string sCommandText)
    {
        int iRowsAffected = db.ExecuteNonQuery(CommandType.Text, sCommandText);
        return iRowsAffected;
    }

    //List of parameters and stored procedure
    public int ExecuteNonQuery(string spName, IList<SqlParameter> sqlParams)
    {
        if (String.IsNullOrEmpty(spName))
            throw new ArgumentException("Stored procedure name cannot be null or empty string.");
        int iRowsAffected = 0;
        using (DbCommand dbCommand = db.GetStoredProcCommand(spName))
        {
            dbCommand.CommandTimeout = 600;
            foreach (SqlParameter sqlParameter in sqlParams)
                dbCommand.Parameters.Add(sqlParameter);
            iRowsAffected = db.ExecuteNonQuery(dbCommand);
            sqlParams.Clear();
            foreach (SqlParameter sqlParameter in dbCommand.Parameters)
                sqlParams.Add(sqlParameter);
        }
        return iRowsAffected;
    }
    
    //No parameters and choice between stored procedure and simple command text
    public int ExecuteNonQuery(bool IsStoredProc, string sCommandText)
    {
        if (String.IsNullOrEmpty(sCommandText))
            throw new ArgumentException("Sql Command Name cannot be null or empty string.");
        int iRowsAffected = 0;
        List<SqlParameter> list = new List<SqlParameter>();
        if (IsStoredProc)
            iRowsAffected = ExecuteNonQuery(sCommandText, list);
        else
            iRowsAffected = ExecuteNonQuery(sCommandText);

        return (iRowsAffected);
    }

    //Most versatile. Choice between stored procedure and Sql Command Text along with list of parameters
    public int ExecuteNonQuery(bool isStoredProc, string sCommandText,
            IList<SqlParameter> sqlParams)
    {
        if (String.IsNullOrEmpty(sCommandText))
            throw new ArgumentException("Command name cannot be null or empty string.");
        int iRowsAffected = 0;
        if (isStoredProc)
            iRowsAffected = ExecuteNonQuery(sCommandText, sqlParams);
        else
        {
            using (DbCommand dbCommand = db.GetSqlStringCommand(sCommandText))
            {
                dbCommand.CommandTimeout = 600;
                foreach (SqlParameter sqlParameter in sqlParams)
                    dbCommand.Parameters.Add(sqlParameter);
                iRowsAffected = db.ExecuteNonQuery(dbCommand);
                sqlParams.Clear();
                foreach (SqlParameter sqlParameter in dbCommand.Parameters)
                    sqlParams.Add(sqlParameter);
            }
        }
        return iRowsAffected;
    }

    //Returns MembershipCreateStatus. Choice between Stored Procedure and Sql Command Text along with list of parameters
    //public MembershipCreateStatus MembershipExecuteNonQuery(bool isStoredProc, string sCommandText,
    //        IList<SqlParameter> sqlParams)
    //{
    //    if (String.IsNullOrEmpty(sCommandText))
    //        throw new ArgumentException("Command name cannot be null or empty string.");
    //    MembershipCreateStatus status;
    //    string sReturnParamName = null;
        
    //    using (DbCommand dbCommand = (isStoredProc) ? db.GetStoredProcCommand(sCommandText) : 
    //        db.GetSqlStringCommand(sCommandText))
    //    {
    //        dbCommand.CommandTimeout = 600;
    //        foreach (SqlParameter sqlParameter in sqlParams)
    //        {
    //            dbCommand.Parameters.Add(sqlParameter);
    //            if (sqlParameter.Direction == ParameterDirection.ReturnValue)
    //                sReturnParamName = sqlParameter.ToString();
    //        }                
    //        db.ExecuteNonQuery(dbCommand);                
    //        status = (MembershipCreateStatus)dbCommand.Parameters[sReturnParamName].Value;
    //        sqlParams.Clear();
    //        foreach (SqlParameter sqlParameter in dbCommand.Parameters)
    //            sqlParams.Add(sqlParameter);
    //    }        
    //    return status;
    //}
    
    #endregion

    #region ExecuteReader

    //simple datareader from a sql command
    public IDataReader ExecuteReader(string sCommandText)
    {
        IDataReader dataReader = db.ExecuteReader(CommandType.Text, sCommandText);
        return dataReader;
    }

    //DataReader from stored proc and parameter list
    public IDataReader ExecuteReader(string spName, IList<SqlParameter> sqlParams)
    {

        if (String.IsNullOrEmpty(spName))
            throw new ArgumentException("Stored procedure name cannot be null or empty string.");

        IDataReader dataReader = null;
        using (DbCommand dbCommand = db.GetStoredProcCommand(spName))
        {

            foreach (SqlParameter sqlParameter in sqlParams)
                dbCommand.Parameters.Add(sqlParameter);
            dataReader = db.ExecuteReader(dbCommand);
            sqlParams.Clear();
            foreach (SqlParameter sqlParameter in dbCommand.Parameters)
                sqlParams.Add(sqlParameter);
        }
        return dataReader;
    }

    //Most Versatile. DataReader from a choice of Stored procedure and command text with parameter list
    public IDataReader ExecuteReader(bool IsStoredProc,
        string sCommandText, IList<SqlParameter> sqlParams)
    {
        if (String.IsNullOrEmpty(sCommandText))
            throw new ArgumentException("Command name cannot be null or empty string.");

        IDataReader dataReader = null;
        if (IsStoredProc)
            dataReader = ExecuteReader(sCommandText, sqlParams);
        else
        {
            using (DbCommand dbCommand = db.GetSqlStringCommand(sCommandText))
            {
                foreach (SqlParameter sqlParameter in sqlParams)
                    dbCommand.Parameters.Add(sqlParameter);
                dataReader = db.ExecuteReader(dbCommand);
                sqlParams.Clear();
                foreach (SqlParameter sqlParameter in dbCommand.Parameters)
                    sqlParams.Add(sqlParameter);
            }
        }
        return dataReader;
    }
    #endregion

    #region ExecuteScalar
    //simple Execute Scalar
    public Object ExecuteScalar(string commandText)
    {
        Object result = db.ExecuteScalar(CommandType.Text, commandText);
        return result;
    }
    //Execute Scalar with stored proc and parameters
    public Object ExecuteScalar(string spName, IList<SqlParameter> sqlParams)
    {

        if (String.IsNullOrEmpty(spName))
            throw new ArgumentException("Stored procedure name cannot be null or empty string.");

        Object result = null;
        using (DbCommand dbCommand = db.GetStoredProcCommand(spName))
        {

            foreach (SqlParameter sqlParameter in sqlParams)
                dbCommand.Parameters.Add(sqlParameter);
            result = db.ExecuteScalar(dbCommand);
            sqlParams.Clear();
            foreach (SqlParameter sqlParameter in dbCommand.Parameters)
                sqlParams.Add(sqlParameter);

        }
        return result;
    }

    //Most Versatile. With Choice of stored proc and Command Text and with Parameters
    public Object ExecuteScalar(bool isStoredProc, string sCommandText, IList<SqlParameter> sqlParams)
    {

        if (String.IsNullOrEmpty(sCommandText))
            throw new ArgumentException("Command name cannot be null or empty string.");

        Object result = null;
        if (isStoredProc)
            ExecuteScalar(sCommandText, sqlParams);
        using (DbCommand dbCommand = db.GetSqlStringCommand(sCommandText))
        {

            foreach (SqlParameter sqlParameter in sqlParams)
                dbCommand.Parameters.Add(sqlParameter);

            result = db.ExecuteScalar(dbCommand);
            sqlParams.Clear();
            foreach (SqlParameter sqlParameter in dbCommand.Parameters)
                sqlParams.Add(sqlParameter);
        }
        return result;
    }
    #endregion

    #region ExecuteDataSet

    //Basic
    public DataSet ExecuteDataSet(string sCommandText)
    {
        DataSet dataSet = db.ExecuteDataSet(CommandType.Text, sCommandText);
        return dataSet;
    }

    //Stored procedure with parameters
    public DataSet ExecuteDataSet(string spName, IList<SqlParameter> sqlParams)
    {

        if (String.IsNullOrEmpty(spName))
            throw new ArgumentException("Stored procedure name cannot be null or empty string.");

        DataSet dataSet = null;
        using (DbCommand dbCommand = db.GetStoredProcCommand(spName))
        {
            foreach (SqlParameter sqlParameter in sqlParams)
                dbCommand.Parameters.Add(sqlParameter);
            dataSet = db.ExecuteDataSet(dbCommand);
            //assign the output parameter values if any.
            sqlParams.Clear();
            foreach (SqlParameter sqlParameter in dbCommand.Parameters)
                sqlParams.Add(sqlParameter);
        }
        return dataSet;
    }

    //Most Vesatile
    public DataSet ExecuteDataSet(bool IsStoredProc,
            string sCommandText, IList<SqlParameter> sqlParams)
    {
        DataSet dataSet;
        if (IsStoredProc)
            dataSet = ExecuteDataSet(sCommandText, sqlParams);
        else
        {
            SqlCommand sqlCommand = (SqlCommand)db.GetSqlStringCommand(sCommandText);
            foreach (SqlParameter sqlParameter in sqlParams)
                sqlCommand.Parameters.Add(sqlParameter);
            dataSet = db.ExecuteDataSet(sqlCommand);
            //assign the output parameter values if any.
            sqlParams.Clear();
            foreach (SqlParameter sqlParameter in sqlCommand.Parameters)
                sqlParams.Add(sqlParameter);
        }
        return dataSet;
    }
    #endregion

    #region LoadDataSet
    //Load the table array of a dataset with data.

    //Basic. Given a dataset and sql command string without parameters. Load the tableName array
    public void LoadDataSet(string sCommandText, DataSet dataset, string[] tableName)
    {
        if (String.IsNullOrEmpty(sCommandText))
            throw new ArgumentException("Sql Command Text cannot be null or empty string.");

        if (dataset == null)
            throw new ArgumentNullException("Dataset cannot be null.");

        if (tableName == null)
            throw new ArgumentException("Table names cannot be null.");

        using (DbCommand dbCommand = db.GetSqlStringCommand(sCommandText))
        {
            db.LoadDataSet(dbCommand, dataset, tableName);
        }
    }

    //Given Stored Proc Name and list of parameters
    public void LoadDataSet(string spName,
        IList<SqlParameter> sqlParams, DataSet dataset, string[] tableName)
    {
        if (String.IsNullOrEmpty(spName))
            throw new ArgumentException("Stored procedure name cannot be null or empty string.");

        if (dataset == null)
            throw new ArgumentNullException("Dataset cannot be null.");

        if (tableName == null)
            throw new ArgumentException("Table names cannot be null.");

        using (DbCommand dbCommand = db.GetStoredProcCommand(spName))
        {
            foreach (SqlParameter sqlParameter in sqlParams)
                dbCommand.Parameters.Add(sqlParameter);
            db.LoadDataSet(dbCommand, dataset, tableName);
            //assign the output parameter values if any.
            sqlParams.Clear();
            foreach (SqlParameter sqlParameter in dbCommand.Parameters)
                sqlParams.Add(sqlParameter);
        }
    }

    // Most Versatile
    public void LoadDataSet(bool isStoredProc, string sCommandText,
            IList<SqlParameter> sqlParams, DataSet dataset, string[] tableName)
    {
        if (String.IsNullOrEmpty(sCommandText))
            throw new ArgumentException("Command Text name cannot be null or empty string.");

        if (dataset == null)
            throw new ArgumentNullException("Dataset cannot be null.");

        if (tableName == null)
            throw new ArgumentException("Table names cannot be null.");

        DbCommand dbCommand;

        if (isStoredProc)
            LoadDataSet(sCommandText, sqlParams, dataset, tableName);
        else
        {
            dbCommand = db.GetSqlStringCommand(sCommandText);
            foreach (SqlParameter sqlParameter in sqlParams)
                dbCommand.Parameters.Add(sqlParameter);
            db.LoadDataSet(dbCommand, dataset, tableName);
            //assign the output parameter values if any.
            sqlParams.Clear();
            foreach (SqlParameter sqlParameter in dbCommand.Parameters)
                sqlParams.Add(sqlParameter);
            dbCommand = null;

        }

    }
    #endregion

    #region IDisposable Members

    public void Dispose()
    {
        db = null;
        GC.Collect();
    }

    #endregion
}
